<?php

/*
 * Create PPI Database/Schema & Tables
 * sjh April 2013
 */

$user_name = "root";
$password = "haddons";
$database = "PPI";
$server = "127.0.0.1";

print "<h1>Create PPI Database</h1><p>";

$db_handle = mysql_connect($server, $user_name, $password);
print "Connection to the Server " . $db_handle . " opened <BR>";

$sql = "CREATE SCHEMA " . $database . " ;";

if (mysql_query($sql)) {
    print "Creating PPI Database<BR>";
} else {
    print "PPI Database already exists<BR>";
}

$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {
    print "<B>connected OK - creating tables</B><p>";
    $sql = "CREATE TABLE `PPI`.`log` (
    `ID` INT NOT NULL AUTO_INCREMENT ,
  `Date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  `Who` VARCHAR(20) NOT NULL ,
  `FileName` VARCHAR(45) NOT NULL ,
  `Key` VARCHAR(45) NOT NULL ,
  `Action` VARCHAR(20) ,
    PRIMARY KEY (`ID`) 
    
  );";

    print $sql . "<br>";
    if (mysql_query($sql)) {
        print "creating log table<br>";
    } else {
        print "log table already exists<BR>";
    }

    $sql = "CREATE  TABLE `PPI`.`client` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `CreateDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  `CreateBy` VARCHAR(20) NULL ,
  `Title` VARCHAR(10) NULL ,
  `FirstNames` VARCHAR(50) NULL ,
  `Surname` VARCHAR(50) NULL ,
  `TelNo` VARCHAR(15) NULL ,
  `Email` VARCHAR(50) NULL ,
  `House` VARCHAR(45) NULL ,
  `Street` VARCHAR(45) NULL ,
  `Town` VARCHAR(45) NULL ,
  `County` VARCHAR(45) NULL ,
  `Postcode` VARCHAR(8) NULL ,
  `Mobile` VARCHAR(15) NULL ,
  `OtherTitle` VARCHAR(10) NULL ,
  `OtherFirstNames` VARCHAR(50) NULL ,
  `OtherSurname` VARCHAR(50) NULL ,
  `OtherTelno` VARCHAR(15) NULL ,
  `OtherEmail` VARCHAR(50) NULL ,
  PRIMARY KEY (`ID`) ,
  UNIQUE KEY `PCode_House` (`Postcode`,`House`) );";

    if (mysql_query($sql)) {
        print "creating client table<br>";
    } else {
        print "client table already exists<BR>";
    }

    $sql = "CREATE  TABLE `PPI`.`users` (
  `login_ID` VARCHAR(20) NOT NULL ,
  `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `password` VARCHAR(45) NOT NULL ,
  `first_name` VARCHAR(45) NULL ,
  `last_name` VARCHAR(45) NULL ,
  `email` VARCHAR(50) NULL ,
  `authorised` INT NULL DEFAULT 0 ,
  PRIMARY KEY (`login_ID`) ,
  UNIQUE INDEX `login_ID_UNIQUE` (`login_ID` ASC) );";

    if (mysql_query($sql)) {
        print "creating user table<br>";
    } else {
        print "user table already exists<BR>";
    }
//
    $sql = "CREATE  TABLE `PPI`.`lenders` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `CreateDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  `CreateBy` VARCHAR(20) NULL ,
  `LenderName` VARCHAR(50) NULL ,
  `Address1` VARCHAR(50) NULL ,
  `Address2` VARCHAR(50) NULL ,
  `Address3` VARCHAR(50) NULL ,
  `City` VARCHAR(45) NULL ,
  `Postcode` VARCHAR(8) NULL ,
  `TelNo` VARCHAR(15) NULL ,
  `FaxNo` VARCHAR(15) NULL ,
  `Email` VARCHAR(50) NULL ,
  `FSCS` VARCHAR(1) NULL ,
  `FOSQ` VARCHAR(1) NULL ,
  PRIMARY KEY (`ID`) );";

    if (mysql_query($sql)) {
        print "creating lender table<br>";
    } else {
        print "lender table already exists<BR>";
    }
//
    $sql = "CREATE  TABLE `PPI`.`claims` (
      `ID` INT NOT NULL AUTO_INCREMENT ,
      `CreateDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
      `CreateBy` VARCHAR(20) NULL ,
      `ClientID` INT NOT NULL ,
      `LenderID` INT NOT NULL ,
      `LoanNo` VARCHAR(25) NULL,
      `LoanStartDate` DATETIME NULL,
      `LoanType` VARCHAR(1) NOT NULL,
      `LoanTypeOther` VARCHAR(30) NULL,
      `LoanAmount` DECIMAL(11,2) NULL ,
      PRIMARY KEY (`ID`),
      KEY `Client_idx` (`ClientID`),
  KEY `Lender_idx` (`LenderID`),
  CONSTRAINT `Lender` FOREIGN KEY (`LenderID`) REFERENCES `lenders` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Client` FOREIGN KEY (`ClientID`) REFERENCES `client` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION );";
//
    mysql_close($db_handle);
}
?>
